<?php
echo "including config<br>";
include_once($_SERVER['DOCUMENT_ROOT'] ."/kse/cfg.php");
include_once($_SERVER['DOCUMENT_ROOT'] ."/kse/names.php");
echo "Connecting to database<br>";
$conn = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die ('Error connecting to mysql');
mysql_select_db(DB_NAME);

$query = "SET NAMES utf8";
$makeresult = mysql_query($query) or die(mysql_error() ."<br>". $query);

echo "Begin exporting db<br>";
include_once("magaz.php");
echo "magaz.php included<br>";	

function export_products($products){
	echo "<strong>Starting exporting products</strong><br>";
	echo "Reacreating products table<br>";
	$query = "DROP TABLE IF EXISTS `kpulse_products`";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);


		$query = "CREATE TABLE IF NOT EXISTS `kpulse_products` (
	  `product_id` bigint(255) NOT NULL auto_increment,
	  `product_code_number` varchar(255) NOT NULL,
	  `product_name` varchar(255)  default NULL,
	  `product_rating` bigint(255) default NULL,
	  `product_voted` bigint(255) default NULL,
	  `product_admin_rating` int(10) NOT NULL,
	  `product_description` text,
	  `product_description_index` smallint(1) NOT NULL,
	  `product_short_des` text NOT NULL,
	  `product_price` float(255,2) default NULL,
	  `product_producer_id` int(255) default NULL,
	  `product_cat_id` int(255) default NULL,
	  `product_publicate` smallint(1) NOT NULL,
	  `product_recomended` smallint(1) NOT NULL,
	  `product_keywords` varchar(255) default NULL,
	  `product_page_des` varchar(255) NOT NULL,
	  `product_producer_tmp` varchar(255) default 1,
	  `product_creator_id` varchar(30) DEFAULT NULL,
  	  `product_created` bigint(20) DEFAULT '0',
  	  `product_editor_id` varchar(30) DEFAULT NULL,
  	  `product_edited` bigint(20) DEFAULT '0',
	  PRIMARY KEY  (`product_id`),
	  KEY `product_id` (`product_id`,`product_cat_id`),
	  KEY `product_code_number` (`product_code_number`),
	  FULLTEXT KEY `product_description` (`product_description`)
	) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=utf8";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);

	foreach($products as $product){
		if(isset($product["voted"]) && $product["voted"]!=""){
		}
		else{
			$product["voted"] = 'NULL';
		}
		if(isset($product["rating"]) && $product["rating"]!=""){
		}
		else{
			$product["rating"] = 'NULL';
		}
		$query = "INSERT INTO kpulse_products SET product_id=". $product["product_id"] .",
												product_code_number='". addslashes($product["code_number"]) ."',
												product_name='". $product["name"] ."',
												product_rating=". $product["rating"] .",
												product_voted=". $product["voted"] .",
												product_admin_rating=". $product["admin_rating"] .",
												product_description=\"". $product["description"] ."\",
												product_description_index=1,
												product_short_des=\"". $product["short_des"] ."\",
												product_price=". $product["price"] .",
												product_producer_id=". $product["producer_id"] .",
												product_cat_id=". $product["cat_id"] .",
												product_publicate=". $product["publicate"] .",
												product_recomended=". $product["recomended"] .",
												product_keywords='". addslashes($product["keywords"]) ."',
												product_page_des=\"". $product["page_des"] ."\",
												product_creator_id=1,
											  	product_created=". time().",
											  	product_editor_id=1,
											  	product_edited=". time(); 

		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
		echo "Product ". $product["name"] ." added<br>";
	}
}

function export_producers($producers){
echo "<strong>Starting exporting producers</strong><br>";
	echo "Reacreating producers table<br>";
	$query = "DROP TABLE IF EXISTS `kpulse_producers`";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);


		$query = "CREATE TABLE IF NOT EXISTS `kpulse_producers` (
	  `producer_id` bigint(255) NOT NULL auto_increment,
	  `producer_id_old` int(255) NOT NULL,
	  `producer_name` varchar(255) NOT NULL,
	  `producer_description` text,
	  `producer_link` varchar(255) default NULL,
	  `producer_keywords` varchar(255) default NULL,
	  `producer_page_des` varchar(255) NOT NULL,
	  `producer_creator_id` varchar(30) DEFAULT NULL,
  	  `producer_created` bigint(20) DEFAULT '0',
  	  `producer_editor_id` varchar(30) DEFAULT NULL,
  	  `producer_edited` bigint(20) DEFAULT '0',
	  PRIMARY KEY  (`producer_id`),
	  FULLTEXT KEY `producer_description` (`producer_description`)
	) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	foreach($producers as $producer){
		$query = "INSERT INTO kpulse_producers SET producer_id_old=". $producer["producer_id"] .",
													producer_name='". addslashes($producer["name"]) ."',
													producer_description=\"". addslashes($producer["description"]) ."\",
													producer_link='". addslashes($producer["link"]) ."/',
													producer_keywords='". addslashes($producer["keywords"]) ."',
													producer_page_des=\"". $producer["page_des"] ."\",
													producer_creator_id=1,
												  	producer_created=". time().",
												  	producer_editor_id=1,
												  	producer_edited=". time(); 
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
		echo "Producer ". $producer["name"] ." added<br>";
	}
	echo "RECREATING id's<br>";
	$query = "SELECT producer_id_old, producer_id FROM kpulse_producers";
	$result2 = mysql_query($query) or die(mysql_error() ."<br>". $query);
	while($row=mysql_fetch_array($result2, MYSQL_ASSOC)){
		$query = "UPDATE kpulse_products SET product_producer_id=". $row["producer_id"] .", product_producer_tmp=0 WHERE product_producer_id=". $row["producer_id_old"] ." AND product_producer_tmp=1";
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	}
	echo "REMOVING unused columns<br>";
	$query = "ALTER TABLE kpulse_producers DROP producer_id_old";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	
	$query = "UPDATE kpulse_products SET product_producer_id=0 WHERE product_producer_tmp=1";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	
	$query = "ALTER TABLE kpulse_products DROP product_producer_tmp";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
}

function export_categories($arr){
	echo "<strong>Starting exporting categories</strong><br>";
	echo "Reacreating categories table<br>";
	$query = "DROP TABLE IF EXISTS `kpulse_categories`";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	
$query = "CREATE TABLE IF NOT EXISTS `kpulse_categories` (
	`cat_id` int(255) NOT NULL auto_increment,
	`cat_old` int(255) NOT NULL,
	`cat_parent_id` int(255) NOT NULL,
	`cat_lft` int(11) default NULL,
	`cat_rgt` int(11) default NULL,
	`cat_name` varchar(255) default NULL,
	`cat_keywords` varchar(255) default NULL,
	`cat_link` varchar(255) default NULL,
	`cat_visible` smallint(1) NOT NULL default '0',
	`cat_description` text NOT NULL,
	`cat_page_des` varchar(255) NOT NULL,
	`cat_title` varchar(255) NOT NULL,
	`cat_creator_id` varchar(30) DEFAULT NULL,
  	`cat_created` bigint(20) DEFAULT '0',
  	`cat_editor_id` varchar(30) DEFAULT NULL,
  	`cat_edited` bigint(20) DEFAULT '0',
	PRIMARY KEY  (`cat_id`),
	KEY `cat_link` (`cat_link`)
)  ENGINE=MyISAM AUTO_INCREMENT=1  DEFAULT CHARSET=utf8";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);

	//sort($categories);
	$arr = array_sort($arr, 'cat_id', SORT_ASC);
	foreach($arr as $categorie){
		$query = "INSERT INTO kpulse_categories SET cat_old=". $categorie["cat_id"] .",
													cat_parent_id=". $categorie["parent_id"] .",
													cat_name='". addslashes($categorie["name"]) ."',
													cat_keywords='". addslashes($categorie["keywords"]) ."',
													cat_link='". addslashes($categorie["link"]) ."',
													cat_visible=". $categorie["visible"] .",
													cat_description=\"". $categorie["description"] ."\",
													cat_page_des=\"". $categorie["page_des"] ."\",
													cat_title='". addslashes($categorie["title"]) ."',
													cat_creator_id=1,
												  	cat_created=". time().",
												  	cat_editor_id=1,
												  	cat_edited=". time();
		 
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
		echo "Categorie ". $categorie["name"] ." added<br>";
	}
	echo "Modify ROOT category<br>";
	$query = "UPDATE kpulse_categories SET cat_link='all/', cat_lft=1, cat_rgt=2 WHERE cat_name='ROOT'";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	echo "Assigning lft, rgt values for new architexture<br>";

	$query = "SELECT * FROM kpulse_categories WHERE cat_lft IS NULL";
	$resultgeneral = mysql_query($query) or die(mysql_error() ."<br>". $query);
	$unsortedcats = array();
	while($rowgeneral=mysql_fetch_array($resultgeneral, MYSQL_ASSOC)){
		$unsortedcats[] = $rowgeneral;
	}
	sortcats($unsortedcats);
	//@die;
	echo "UPDATING products id<br>";
	$query = "SELECT * FROM kpulse_categories";
	$result2 = mysql_query($query) or die(mysql_error() ."<br>". $query);
	while($row=mysql_fetch_array($result2, MYSQL_ASSOC)){
		$query = "UPDATE kpulse_products SET product_cat_id=". $row["cat_id"] ." WHERE product_cat_id=". $row["cat_old"];
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	}
	
	echo "REMOVING unused columns<br>";
	$query = "ALTER TABLE kpulse_categories DROP cat_old";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	
	$query = "ALTER TABLE kpulse_categories DROP cat_parent_id";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	
	echo "ADDING trailing slash to cat links<br>";
	$query = "SELECT * FROM kpulse_categories WHERE cat_id>1";
	$result1 = mysql_query($query) or die(mysql_error() ."<br>". $query);
	while($row=mysql_fetch_array($result1, MYSQL_ASSOC)){
		$query = "UPDATE kpulse_categories SET cat_link='". addslashes($row["cat_link"]) ."/' WHERE cat_id=". $row["cat_id"];
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	}
	
	echo "RENAMING links for new architexture<br>";
	$query = "SELECT * FROM kpulse_categories WHERE cat_id>1";
	$result3 = mysql_query($query) or die(mysql_error() ."<br>". $query);
	while($row=mysql_fetch_array($result3, MYSQL_ASSOC)){
		$query = "SELECT GROUP_CONCAT(parent.cat_link ORDER BY parent.cat_lft SEPARATOR '')
		FROM kpulse_categories AS node,
		kpulse_categories AS parent
		WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
		AND node.cat_id = ". $row["cat_id"] ."
		ORDER BY parent.cat_lft";
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
		
		list($newlink) = mysql_fetch_row($result);
		$newlinksarr[$row["cat_id"]] = $newlink;
	}
	foreach($newlinksarr as $id => $link){
		$query = "UPDATE kpulse_categories SET cat_link='". addslashes($link) ."' WHERE cat_id=". $id;
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	}
}

function export_photos($arr){
	echo "<strong>Starting exporting pictures</strong><br>";
	echo "Reacreating pictures table<br>";
	$query = "DROP TABLE IF EXISTS `kpulse_photos`";
	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);

	$query = "CREATE TABLE IF NOT EXISTS `kpulse_photos` (
	  `photo_id` bigint(255) NOT NULL auto_increment,
	  `photo_product_id` bigint(255) NOT NULL,
	  `photo_large_img` varchar(255) NOT NULL,
	  `photo_small_img` varchar(255) NOT NULL,
	  `photo_tiny_img` varchar(255) NOT NULL,
	  `photo_cat_logo` tinyint(1) NOT NULL,
	  `photo_alt` varchar(255) NOT NULL,
	  PRIMARY KEY  (`photo_id`),
	  KEY `product_id` (`photo_product_id`)
	 )ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4248 ";

	$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
	foreach($arr as $photo){
		$query = "INSERT INTO `kpulse_photos` (`photo_id`, `photo_product_id`, `photo_large_img`, `photo_small_img`, `photo_tiny_img`, `photo_cat_logo`, `photo_alt`) VALUES
		(". $photo["photo_id"] .", ". $photo["product_id"] .",'". $photo["large_img"] ."', '". $photo["small_img"] ."', '". $photo["tiny_img"] ."', ". $photo["cat_logo"] .", '". addslashes($photo["alt"]) ."')";
		$result = mysql_query($query) or die(mysql_error() ."<br>111111111111111". $query);		
	}	
}


function array_sort($array, $on, $order=SORT_ASC)
{
    $new_array = array();
    $sortable_array = array();

    if (count($array) > 0) {
        foreach ($array as $k => $v) {
            if (is_array($v)) {
                foreach ($v as $k2 => $v2) {
                    if ($k2 == $on) {
                        $sortable_array[$k] = $v2;
                    }
                }
            } else {
                $sortable_array[$k] = $v;
            }
        }

        switch ($order) {
            case SORT_ASC:
                asort($sortable_array);
            break;
            case SORT_DESC:
                arsort($sortable_array);
            break;
        }

        foreach ($sortable_array as $k => $v) {
            $new_array[$k] = $array[$k];
        }
    }
    return $new_array;
}

function sortcats($inputarray){
	foreach($inputarray as $inputelemnt){
		
		$query = "SELECT cat_rgt, cat_lft, cat_name, cat_old FROM kpulse_categories WHERE cat_old='". $inputelemnt["cat_parent_id"] ."' AND cat_lft IS NOT NULL";
		$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
		//DEBUG
		echo "<pre>". $inputelemnt["cat_id"] ."-". $inputelemnt["cat_name"];
		print_r($query);
		
		
		if(mysql_num_rows($result) > 0){
			$row = mysql_fetch_row($result);
			//DEBUG
			print_r($row);
			echo "</pre>";
				
			if(($row[0] - $row[1] < 2)){//check if done is leaf --has no childs
				//DEBUG
				echo "<br>Parent category is leaf";
				
				//leaf
				$query = "LOCK TABLE kpulse_categories WRITE";
				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
				
				$query = "SELECT cat_lft FROM kpulse_categories WHERE cat_old =". $inputelemnt["cat_parent_id"]; 
				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
				list($mylft) = mysql_fetch_row($result);
				echo "<br>Parent left - ". $mylft;
				//print_r($rowgeneral);
				if(mysql_num_rows($result) > 0){
					$query = "UPDATE kpulse_categories SET cat_rgt = cat_rgt + 2 WHERE cat_rgt > ". $mylft ."";
					$result = mysql_query($query) or die(mysql_error() ."<br>111111111111111". $query);
					
					$query = "UPDATE kpulse_categories SET cat_lft = cat_lft + 2 WHERE cat_lft > ". $mylft ."";
					$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
					
					$query = "UPDATE kpulse_categories SET cat_lft=". ($mylft  + 1) .", cat_rgt=". ($mylft  + 2) ." WHERE cat_id=". $inputelemnt["cat_id"];
					$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
					
					$query = "UNLOCK TABLES";
					$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
				}
				else{
					$unsortedarray[] = $inputelemnt;
				}
			}
			else{
			
			//find last subbordinate child
				$query = "
				
				SELECT node.cat_id, (COUNT(parent.cat_name) - (sub_tree.depth + 1)) AS depth
				FROM kpulse_categories AS node,
					kpulse_categories AS parent,
					kpulse_categories AS sub_parent,
					(
						SELECT node.cat_name, (COUNT(parent.cat_name) - 1) AS depth
						FROM kpulse_categories AS node,
						kpulse_categories AS parent
						WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
						AND node.cat_old = '". $inputelemnt["cat_parent_id"] ."'
						GROUP BY node.cat_name
						ORDER BY node.cat_lft
					)AS sub_tree
				WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
					AND node.cat_lft BETWEEN sub_parent.cat_lft AND sub_parent.cat_rgt
					AND sub_parent.cat_name = sub_tree.cat_name
				GROUP BY node.cat_name
				HAVING depth = 1
				ORDER BY node.cat_lft DESC LIMIT 1";
				
				//DEBUG
				echo "<br>Parent category is leaf";
				echo $query; 
				

				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
				$row = mysql_fetch_row($result);
				
				//DEBUG
				print_r($row);
				

				//INSERTING VALUES
				$query = "LOCK TABLE kpulse_categories WRITE";
				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);

				$query = "SELECT cat_rgt FROM kpulse_categories WHERE cat_id = '". $row[0] ."'";
				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
				list($myrgt) = mysql_fetch_row($result);
				//DEBUG
				echo "<br>Parent left - ". $myrgt;
				
				$query = "UPDATE kpulse_categories SET cat_rgt = cat_rgt + 2 WHERE cat_rgt > ". $myrgt ."";
				$result = mysql_query($query) or die(mysql_error() ."<br>2222222222222222222". $query);
				
				$query = "UPDATE kpulse_categories SET cat_lft = cat_lft + 2 WHERE cat_lft > ". $myrgt ."";
				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
				
				$query = "UPDATE kpulse_categories SET cat_lft=". ($myrgt  + 1) .", cat_rgt=". ($myrgt  + 2) ." WHERE cat_id=". $inputelemnt["cat_id"];
				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
				
				$query = "UNLOCK TABLES";
				$result = mysql_query($query) or die(mysql_error() ."<br>". $query);
			}
		}
		else{
			$unsortedarray[] = $inputelemnt;
		}
	}
	if(!empty($unsortedarray)){
		sortcats($unsortedarray);
	}
}

export_products($products);
export_categories($categories);
export_producers($producers);
export_photos($photos);
?>
